with f as (select id
                , appeal_no
                , waybill_no
                , user_name
                , phone
                , email
                , status
                , type
                , cause
                , complaint_time
                , province_id
                , province_desc
                , city_id
                , city_desc
                , description
                , sender_name
                , sender_phone
                , sender_address
                , send_network_id
                , send_network_code
                , send_network_name
                , receiver_name
                , receiver_phone
                , receiver_address
                , dispatch_network_id
                , dispatch_network_code
                , dispatch_network_name
                , accept_network_id
                , accept_network_code
                , accept_network_name
                , accept_time
                , assign_time
                , assign_type
                , accept_user_id
                , accept_user_code
                , accept_user_name
                , is_upgrade
                , upgrade_network_id
                , upgrade_network_code
                , upgrade_network_name
                , upgrade_time
                , process_content
                , process_user_id
                , process_user_code
                , process_user_name
                , process_time
                , create_time
                , update_time
                , block_status
                , virtual_phone
                , block_reason
                , virtual_send_phone
                , accept_network_type
                , accept_proxy_id
                , accept_proxy_code
                , accept_proxy_name
                , accept_franchisee_id
                , accept_franchisee_code
                , accept_franchisee_name
                , monitoring_time
                , close_time
                , process_duration
                , response_duration
                , is_monitor_history
                , audit_status
                , reject_status
                , accept_network_mr_code
                , accept_network_mr_name
                , transfer_order_time
                , resp_status
                , channel
                , delivery_code
                , delivery_name
                , is_dispatcher
                , audit_user_id
                , audit_user_code
                , audit_user_name
                , is_again
                , receiver_province_name
                , receiver_province_id
                , receiver_city_name
                , receiver_city_id
                , receiver_area_name
                , receiver_area_id
                , product_type
                , row_number() over (partition by waybill_no order by update_time desc) as rank
           from jms_ods.complaint_info
           where dt between date_sub('{{ execution_date | cst_ds }}', 70) and '{{ execution_date | cst_ds }}'
             and date(create_time) between date_sub('{{ execution_date | cst_ds }}', 60)
               and '{{ execution_date | cst_ds }}')
insert overwrite table jms_dwd.dwd_complaint_info_dt partition ( dt )
select id
     , appeal_no
     , waybill_no
     , user_name
     , phone
     , email
     , status
     , type
     , cause
     , complaint_time
     , province_id
     , province_desc
     , city_id
     , city_desc
     , description
     , sender_name
     , sender_phone
     , sender_address
     , send_network_id
     , send_network_code
     , send_network_name
     , receiver_name
     , receiver_phone
     , receiver_address
     , dispatch_network_id
     , dispatch_network_code
     , dispatch_network_name
     , accept_network_id
     , accept_network_code
     , accept_network_name
     , accept_time
     , assign_time
     , assign_type
     , accept_user_id
     , accept_user_code
     , accept_user_name
     , is_upgrade
     , upgrade_network_id
     , upgrade_network_code
     , upgrade_network_name
     , upgrade_time
     , process_content
     , process_user_id
     , process_user_code
     , process_user_name
     , process_time
     , create_time
     , update_time
     , block_status
     , virtual_phone
     , block_reason
     , virtual_send_phone
     , accept_network_type
     , accept_proxy_id
     , accept_proxy_code
     , accept_proxy_name
     , accept_franchisee_id
     , accept_franchisee_code
     , accept_franchisee_name
     , monitoring_time
     , close_time
     , process_duration
     , response_duration
     , is_monitor_history
     , audit_status
     , reject_status
     , accept_network_mr_code
     , accept_network_mr_name
     , transfer_order_time
     , resp_status
     , channel
     , delivery_code
     , delivery_name
     , is_dispatcher
     , audit_user_id
     , audit_user_code
     , audit_user_name
     , is_again
     , receiver_province_name
     , receiver_province_id
     , receiver_city_name
     , receiver_city_id
     , receiver_area_name
     , receiver_area_id
     , product_type
     , date(create_time) as dt
from f
where rank = 1
    distribute by dt;
